package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select ci.chapter_name `name`,count(ucp.chapter_id) `num1`,\n" +
            "sum(ucp.position_sec) `time`,\n" +
            "count(DISTINCT ucp.user_id) `num2`,\n" +
            "sum(ucp.position_sec)/count(DISTINCT ucp.user_id) `avgTime`\n" +
            "from user_chapter_process ucp \n" +
            "LEFT JOIN chapter_info ci ON ci.id=ucp.chapter_id\n" +
            "GROUP BY ci.chapter_name")
    List<Sugar> getList1();

    @Select("select a.`name`,count(*) `value` from \n" +
            "(select score,\n" +
            "case \n" +
            "when score < 60 then '[0,60)'\n" +
            "when score >=60 and score < 80 then '[60,80)'\n" +
            "when score >=80 and score < 90 then '[80,90)'\n" +
            "when score >=90 and score < 95 then '[90,95)'\n" +
            "when score >=95 and score <= 100 then '[95,100)'\n" +
            "END `name`\n" +
            "from test_exam) a \n" +
            "GROUP BY a.`name`")
    List<Sugar2> getList2();

    @Select("select p.`name`,sum(o.final_amount) `value` from order_info o \n" +
            "LEFT JOIN base_province p ON o.province_id=p.id\n" +
            "GROUP BY p.`name`")
    List<Sugar2> getList3();

    @Select("select sum(final_amount) `value` from order_info \n")
    double getList4();

    @Select("select hour(create_time) `name`,count(*) `value` from user_chapter_process GROUP BY hour(create_time)")
    List<Sugar2> getList5();

    @Select("select bs.source_site `name`,\n" +
            "sum(o.final_amount) `total`,\n" +
            "count(DISTINCT o.user_id) `num1`,\n" +
            "count(o.id) `num2`\n" +
            "from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN course_info ci ON ci.id=d.course_id \n" +
            "LEFT JOIN chapter_info c ON ci.id=c.course_id \n" +
            "LEFT JOIN video_info vi ON c.id=vi.chapter_id \n" +
            "LEFT JOIN base_source bs ON vi.video_source_id=bs.id \n" +
            "where bs.source_site is not null \n" +
            "GROUP BY bs.source_site")
    List<Sugar3> getList6();

    @Select("select \n" +
            "(select count(*) from user_info where create_time='2021-4-23')\n" +
            "-\n" +
            "(select count(*) from user_info where create_time='2021-4-24') `value`")
    double getList7();

    @Select("select ci.course_name `name`,sum(o.final_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN course_info ci ON ci.id=d.course_id \n" +
            "GROUP BY ci.course_name")
    List<Sugar2> getList8();
}
